# 数据库   excel     数据库
#封装 数据库 方法
import json
import os
from pprint import pprint

import pymysql
import xlrd
import yaml

from config import BASE_DIR


# 连接数据库
def db_utils(sql: str, host="127.0.0.1", user="root", password="123456", db="tpshop2.0", port=3306):
    connection = pymysql.connect(host=host, user=user, password=password, db=db, port=port)
    cursor = connection.cursor()
    try:
        cursor.execute(sql)
        if sql.strip().lower().startswith("select"):
            result = cursor.fetchall()
            return result
        else:
            connection.commit()
    except Exception as e:
        connection.rollback()
        raise e
    finally:
        cursor.close()
        connection.close()

# 读取excel表格  配合参数化装饰器
def read_excel(file_name:str,sheet_name:str,start_row:int=0)->list[tuple]:
    file_path = BASE_DIR+"/data/"+file_name
    wb = xlrd.open_workbook(file_path) #打开excel文件
    sheet = wb.sheet_by_name(sheet_name) #通过文件对象创建表格对象
    new_list=[] #创建一个空列表
    for i in range(start_row,sheet.nrows): #循环遍历每一行
        new_list.append(tuple(sheet.row_values(i))) #将每一行的数据转换为元组，并添加到列表中
    return new_list #返回包含所有行数据的列表
#读取yaml文件
def read_yaml(file_name:str)->dict:
    file_path = BASE_DIR+"/config/"+file_name
    with open(file_path,encoding="utf-8") as f:
        return yaml.load(f,Loader=yaml.FullLoader)

#读取json文件
def    read_json(file_name:str,key:str):
    file_path = BASE_DIR+"/data/"+file_name
    with open(file_path,encoding="utf-8") as f:
        list_data = [] #创建一个空列表
        json_data = json.load(f) #json数据转换为字典
        for key in json_data[key]: # json_data[key]或者key对应的数据
           list_data.append(tuple(key.values())) #将字典转换为元组，并添加到列表中
    return list_data


if __name__ == '__main__':
   print(read_json("login.json", "login"))
    # phone="13522963901"
    # sql = f"delete from tp_users where mobile='{phone}'"
    # print(sql)
    # # db_utils(sql)
    # # print(read_excel("data.xls", "login_data", 1))


